Option Strict On

Imports Excel = Microsoft.Office.Interop.Excel
'Imports Office = Microsoft.Office.Core
Imports System.Text.RegularExpressions
Imports System.Runtime.InteropServices

Public Class TemplateAreaFormula
    Implements ITemplateFormula

    Private initialFormula As String
    Private _location As Excel.Range
    Private matchPattern As String = "(?<ref>\w{1,2}\d{1,5}):\k<ref>"

    Friend Sub New(ByRef location As Excel.Range)
        Me._location = location
        Me.initialFormula = location.Formula.ToString()
        InitReference(initialFormula)
    End Sub

    Private Sub InitReference(ByVal formula As String)
        Dim match As Match = Regex.Match(formula, matchPattern)
        While (match.Success)
            If (Not match.Value = String.Empty) Then
                reference += match.Value & ";"
                match = match.NextMatch
            End If
        End While
        reference = reference.TrimEnd(";"c)
    End Sub

    Public Sub ReleaseFormula() Implements ITemplateFormula.ReleaseFormula
        ReleaseComObject(_location)
    End Sub

    Public Sub PreDeployFormula(ByVal rowCount As Integer) Implements ITemplateFormula.PreDeployFormula
        Me._rowCount = rowCount
        Me._isPreDeployed = True
    End Sub

    Public Sub DeployFormula() Implements ITemplateFormula.DeployFormula
        Me.offset = RowCount
        Dim i As Integer
        _location.Formula = UpdateFormula()
        _isDeployed = True
    End Sub

    Private Function UpdateFormula() As String
        Dim result As String = Regex.Replace(initialFormula, matchPattern, _
           AddressOf CapText)
        Return result
    End Function

    Private offset As Int32
    Private Function CapText(ByVal m As Match) As String
        Dim oldArea As String = m.ToString()
        Dim firstCell As Excel.Range = _location.Worksheet.Range(oldArea)
        Try
            Return _location.Worksheet.Range(firstCell, _
                firstCell.Offset(Me.offset - 1, 0)).AddressLocal
        Finally
            ReleaseComObject(firstCell)
        End Try
    End Function

    Private reference As String
    Public ReadOnly Property ReferenceAddress() As String Implements ITemplateFormula.ReferenceAddress
        Get
            Return Me.reference
        End Get
    End Property

    Public Function HasIntersection(ByRef range As Microsoft.Office.Interop.Excel.Range) As Boolean Implements ITemplateFormula.HasIntersection
        Dim o As Object
        Try
            o = range.Application.Range(range.AddressLocal & " " & ReferenceAddress).Value
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Overloads Function HasIntersection1(ByVal address As String) As Boolean Implements ITemplateFormula.HasIntersection
        Dim o As Object
        Try
            Dim range As Microsoft.Office.Interop.Excel.Range = _location.Application.Range(address & " " & ReferenceAddress)
            Try
                o = range.Value
            Finally
                ReleaseComObject(range)
            End Try
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Overloads Function IsReferenceOnMe(ByVal areaReference As String) As Boolean Implements ITemplateFormula.IsReferenceOnMe
        Dim o As Object
        Try
            Dim range As Microsoft.Office.Interop.Excel.Range = _location.Worksheet.Range(areaReference & " " & _location.AddressLocal)
            Try
                o = range.Value
            Finally
                ReleaseComObject(range)
            End Try
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Private _rowCount As Integer = -1
    Public ReadOnly Property RowCount() As Integer Implements ITemplateFormula.RowCount
        Get
            Return Me._rowCount
        End Get
    End Property

    Private _isDeployed As Boolean = False
    Public ReadOnly Property IsDeployed() As Boolean Implements ITemplateFormula.IsDeployed
        Get
            Return Me._isDeployed
        End Get
    End Property

    Private _isPreDeployed As Boolean = False
    Public ReadOnly Property IsPreDeployed() As Boolean Implements ITemplateFormula.IsPreDeployed
        Get
            Return Me._isPreDeployed
        End Get
    End Property

End Class
